/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package xml.survey.utils;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import xml.survey.binding.survey.Survey;
import xml.survey.binding.survey.Surveys;
import xml.survey.object.AnswerObj;
import xml.survey.object.QuestionObj;
import xml.survey.object.SurveyObj;

/**
 *
 * @author CHIP
 */
public class DBUtils {

    public static Connection makeConnection() {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url = "jdbc:sqlserver://localhost:1433;DatabaseName=Survey";
            Connection con = DriverManager.getConnection(url, "sa", "123456");
            return con;
        } catch (SQLException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        } catch (ClassNotFoundException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        }
        return null;
    }

    public static int countSurvey() {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        int count = 0;
        try {
            con = DBUtils.makeConnection();
            call = con.prepareCall("{call countSurvey()}");
            rs = call.executeQuery();

            while (rs.next()) {
                count = rs.getInt("countSurvey");

            }
        } catch (SQLException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (call != null) {
                    call.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
            }
        }
        return count + 1;
    }

    public static int getQuestionID(QuestionObj question) {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        int questionID = 0;
        try {
            con = DBUtils.makeConnection();
            call = con.prepareCall("{call getQuestionID(?,?,?)}");
            call.setString(1, question.getQuestionName());
            call.setString(2, question.getQuestionDesciption());
            call.setInt(3, question.getSurveyID());
            rs = call.executeQuery();

            while (rs.next()) {
                questionID = rs.getInt("questionID");

            }
        } catch (SQLException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (call != null) {
                    call.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
            }
        }
        return questionID;
    }

    public static boolean insertSurvey(SurveyObj survey) {
        Connection con = null;
        CallableStatement call = null;

        try {
            con = DBUtils.makeConnection();
            call = con.prepareCall("{call insertSurvey(?,?,?,?,?)}");
            call.setInt(1, survey.getSurveyID());
            call.setString(2, survey.getSurveyName());
            call.setString(3, survey.getUsername());
            call.setString(4, survey.getSurveyDescription());
            call.setString(5, survey.getSurveyLink());
            int row = call.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (Exception e) {
        }
        return false;
    }

    public static boolean insertquestion(QuestionObj question) {
        Connection con = null;
        CallableStatement call = null;

        try {
            con = DBUtils.makeConnection();
            call = con.prepareCall("{call insertQuestion(?,?,?)}");
            call.setString(1, question.getQuestionName());
            call.setString(2, question.getQuestionDesciption());
            call.setInt(3, question.getSurveyID());
            int row = call.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (Exception e) {
        }
        return false;
    }

    public static boolean insertAnswer(AnswerObj answer) {
        Connection con = null;
        CallableStatement call = null;

        try {
            con = DBUtils.makeConnection();
            call = con.prepareCall("{call insertAnswer(?,?,?)}");
            call.setString(1, answer.getAnswerDescription());
            call.setInt(2, answer.getQuestionID());
            call.setInt(3, answer.getSurveyID());
            int row = call.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (Exception e) {
        }
        return false;
    }

    public static List<Surveys> getSurveys() {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            // Make connection
            con = DBUtils.makeConnection();

            // Call procedure getListInfo from SQL
            call = con.prepareCall("{call getSurveyInfomations()}");
            rs = call.executeQuery();

            Surveys surveys = new Surveys();

            // Declare List to store informations
            List<Surveys> list = new ArrayList<Surveys>();

            // Declare attribute of information object
            int surveyID = 0;
            String surveyName = "";
            String username = "";
            String surveyDescription = "";
            String surveyLink = "";
            while (rs.next()) {
                surveyID = rs.getInt("surveyLink");
                surveyName = rs.getString("surveyName");
                username = rs.getString("username");
                surveyDescription = rs.getString("surveyDescription");
                surveyLink = rs.getString("surveyLink");

                // New object survey
                Survey surveyObj = new Survey(surveyID, surveyName, username, surveyDescription, surveyLink);
                surveys.getSurvey().add(surveyObj);
            }
            // Add to list.
            list.add(surveys);
            return list;
        } catch (SQLException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (call != null) {
                    call.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
            }
        }
        return null;
    }
}
